package dao;

import domin.OrdinaryUser;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;

@Slf4j public final class OrdinaryUserDao {
    private static OrdinaryUserDao ordinaryUserDao = new OrdinaryUserDao();
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    private OrdinaryUserDao() {
    }
    public static OrdinaryUserDao getInstance() {
        return ordinaryUserDao;
    }
    public Collection<OrdinaryUser> findAll(Connection connection, Collection<Condition> condition, Pagination pagination) throws SQLException {
        Collection<OrdinaryUser> ordinaryUsers = new TreeSet<OrdinaryUser>();
        int totalNum = CommonDao.getInstance().count(connection,"OrdinaryUser");
        String sql_select_all = "SELECT * FROM ordinaryUser ";
        if (condition != null){
            sql_select_all += Condition.toWhereClause(JSON.toJSONString(condition));
            log.trace(sql_select_all);
        }
        if (pagination != null){
            sql_select_all += pagination.toLimitClause(totalNum);
            log.trace(sql_select_all);
        }
        this.preparedStatement = connection.prepareStatement(sql_select_all);
        this.resultSet = preparedStatement.executeQuery();
        while (this.resultSet.next()){
            int id = resultSet.getInt("id");
            String no = resultSet.getString("no");
            String name = resultSet.getString("name");
            String contactInformation = resultSet.getString("contactInformation");
            String appointmentInformation = resultSet.getString("appointmentInformation");
            OrdinaryUser foundOrdinaryUser = new OrdinaryUser(id,no,name,contactInformation,appointmentInformation);
            ordinaryUsers.add(foundOrdinaryUser);
        }
        JdbcHelper.close(this.resultSet,this.preparedStatement);
        return ordinaryUsers;
    }
    public OrdinaryUser find(Connection connection, Integer id) throws SQLException {
        OrdinaryUser foundOrdinaryUser = new OrdinaryUser();
        this.preparedStatement = connection.prepareStatement("select * from OrdinaryUser where id=" +id);
        this.resultSet = this.preparedStatement.executeQuery();
        this.resultSet.next();
        foundOrdinaryUser.setId(this.resultSet.getInt("id"));
        foundOrdinaryUser.setNo(this.resultSet.getString("no"));
        foundOrdinaryUser.setName(this.resultSet.getString("name"));
        foundOrdinaryUser.setAppointmentInformation(this.resultSet.getString("appointmentInformation"));
        foundOrdinaryUser.setContactInformation(this.resultSet.getString("contactInformation"));
        JdbcHelper.close(resultSet,preparedStatement);
        return foundOrdinaryUser;
    }
    public Collection<OrdinaryUser> getAllWithCondition(Connection connection, String s) throws SQLException{
        Collection<OrdinaryUser> ordinaryUsers = new HashSet<OrdinaryUser>();
        String value = "%" + s + "%";
        value=" \"" + value + "\"";
        PreparedStatement preparedStatement = connection.prepareStatement("" +
                "select * from OrdinaryUser where " +
                "name like " + value
                +" OR no like" + value +"OR contactInformation like" + value);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            OrdinaryUser ordinaryUser = null;
            Integer id = resultSet.getInt("id");
            String no = resultSet.getString("no");
            String name = resultSet.getString("name");
            String contactInformation = resultSet.getString("contactInformation");
            String appointmentInformation = resultSet.getString("appointmentInformation");
            ordinaryUser = new OrdinaryUser(id,no,name,contactInformation,appointmentInformation);
            ordinaryUsers.add(ordinaryUser);
        }
        JdbcHelper.close(null,preparedStatement);
        return ordinaryUsers;
    }

    public boolean update(Connection connection, OrdinaryUser ordinaryUser) throws SQLException {
        this.preparedStatement = connection.prepareStatement("update OrdinaryUser set no=?,name=?,contactInformation=?,appointmentInformation=? where id=?");
        this.preparedStatement.setString(1, ordinaryUser.getNo());
        this.preparedStatement.setString(2, ordinaryUser.getName());
        this.preparedStatement.setString(3, ordinaryUser.getContactInformation());
        this.preparedStatement.setString(4, ordinaryUser.getAppointmentInformation());
        this.preparedStatement.setInt(5, ordinaryUser.getId());
        int rowAffected = this.preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected > 0;
    }
    public boolean add(Connection connection, OrdinaryUser ordinaryUser) throws SQLException {
        this.preparedStatement = connection.prepareStatement("INSERT INTO OrdinaryUser (no,name,contactInformation,appointmentInformation) VALUES (?,?,?,?)");
        preparedStatement.setString(1, ordinaryUser.getNo());
        preparedStatement.setString(2, ordinaryUser.getName());
        preparedStatement.setString(3, ordinaryUser.getContactInformation());
        preparedStatement.setString(4, ordinaryUser.getAppointmentInformation());
        int rowAffected = this.preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

    public boolean delete(Connection connection,Integer id) throws SQLException {
        this.preparedStatement = connection.prepareStatement("delete from OrdinaryUser where id=" + id);
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

}
